using Microsoft.Extensions.Caching.Distributed;
using net_work.Common;
using net_work.Common.Request;
using net_work.Data.DTO.Sys;
using net_work.Data.Enum.Sys;
using net_work.Data.Enum.System;
using net_work.Data.POCO.Sys;
using net_work.IRepository.Sys;
using net_work.IRepository.System;
using net_work.IService.IBackgroundTask.IHandler;
using OfficeOpenXml;
using TaskStatus = net_work.Data.Enum.System.TaskStatus;

namespace net_work.Service.BackgroundTask.Handler;

public class ImportTeacherExcelTaskHandler(IDistributedCache cache, IServiceScopeFactory serviceScopeFactory ) : ITaskHandler
{
    public async Task HandleAsync(TaskRequest taskRequest)
    {
        try
        {
            // 获取存储的文件路径
            var filePath = taskRequest.Parameters[TaskParams.FilePath].ToString();
            if (string.IsNullOrEmpty(filePath) || !File.Exists(filePath))
            {
                Console.WriteLine("文件不存在");
                await cache.SetStringAsync("Task:" + taskRequest.TaskId, TaskStatus.Failed.ToString());
                return;
            }

            await using var stream = new FileStream(filePath, FileMode.Open, FileAccess.Read);
            // 打开 Excel 文件
            using var package = new ExcelPackage(stream);

            var worksheet = package.Workbook.Worksheets[0];
            if (worksheet == null)
            {
                Console.WriteLine("工作表不存在");
                await cache.SetStringAsync("Task:" + taskRequest.TaskId, TaskStatus.Failed.ToString());
                return;
            }

            if (!ValidateHeaderColumns(worksheet, TeacherExcelCells.HeaderColumns))
            {
                Console.WriteLine("表头列名不匹配");
                await cache.SetStringAsync("Task:" + taskRequest.TaskId, TaskStatus.Failed.ToString());
                return;
            }

            if (!ValidateRequiredColumnsFilled(worksheet, TeacherExcelCells.HeaderColumns))
            {
                Console.WriteLine("必填列未填写");
                await cache.SetStringAsync("Task:" + taskRequest.TaskId, TaskStatus.Failed.ToString());
                return;
            }

            if (!ValidateUniqueColumns(worksheet, TeacherExcelCells.HeaderColumns))
            {
                Console.WriteLine("唯一列重复");
                await cache.SetStringAsync("Task:" + taskRequest.TaskId, TaskStatus.Failed.ToString());
                return;
            }

            using var scope = serviceScopeFactory.CreateScope();
            var userContext = scope.ServiceProvider.GetRequiredService<IUserContext>();
            userContext.Username = "admin";
            userContext.UserId = 1;
            userContext.UserRole = "ADMIN";

            var userRepository = scope.ServiceProvider.GetRequiredService<IUserRepository>();
            var personRepository = scope.ServiceProvider.GetRequiredService<IPersonRepository>();
            var teacherRepository = scope.ServiceProvider.GetRequiredService<ITeacherRepository>();
            var collegeRepository = scope.ServiceProvider.GetRequiredService<ICollegeRepository>();
            var departmentRepository = scope.ServiceProvider.GetRequiredService<IDepartmentRepository>();
            var transactionManager = scope.ServiceProvider.GetRequiredService<ITransactionManager>();

            var count = TeacherExcelCells.TipMessages.Count + 3;
            try
            {
                await cache.SetStringAsync("Task:" + taskRequest.TaskId, TaskStatus.Processing.ToString());
                await transactionManager.BeginTransactionAsync();

                for (var row = count + 1; row <= worksheet.Dimension.End.Row; row++)
                {
                    var personAddRequest = new PersonAddRequest
                    {
                        PsnName = worksheet.Cells[row, 1].Text.Trim(),
                        PsnGen = worksheet.Cells[row, 2].Text.Trim(),
                        PsnIdType = worksheet.Cells[row, 3].Text.Trim(),
                        PsnIdNum = worksheet.Cells[row, 4].Text.Trim(),
                        PsnBirDate = DateTime.TryParse(worksheet.Cells[row, 5].Text, out var date) ? date : null,
                        PsnAddr = string.IsNullOrEmpty(worksheet.Cells[row, 6].Text)
                            ? null
                            : worksheet.Cells[row, 6].Text,
                        PsnBirAddr = string.IsNullOrEmpty(worksheet.Cells[row, 7].Text)
                            ? null
                            : worksheet.Cells[row, 7].Text,
                        PsnNat = string.IsNullOrEmpty(worksheet.Cells[row, 8].Text)
                            ? null
                            : worksheet.Cells[row, 8].Text,
                        PsnRen = string.IsNullOrEmpty(worksheet.Cells[row, 9].Text)
                            ? null
                            : worksheet.Cells[row, 9].Text
                    };

                    var personId = await personRepository.InsertAsync(personAddRequest);
                    if (personId <= 0)
                    {
                        throw new Exception("插入人员信息失败");
                    }

                    var pagedAsync = await collegeRepository.GetPagedAsync(new PagedRequest(1, PageSizeEnum.Unlimited,
                    [
                        new FilterCondition("ColleName", FilterOperator.Equals, worksheet.Cells[row, 12].Text.Trim())
                    ]));
                    if (pagedAsync.TotalRecords <= 0)
                    {
                        throw new Exception("学院不存在");
                    }

                    var collegeId = pagedAsync.Data.First().Id;
                    long? departmentId = null;

                    if (!string.IsNullOrEmpty(worksheet.Cells[row, 17].Text.Trim()))
                    {
                        var pagedResult = await departmentRepository.GetPagedAsync(new PagedRequest(1,
                            PageSizeEnum.Unlimited,
                            [
                                new FilterCondition("DepName", FilterOperator.Equals,
                                    worksheet.Cells[row, 17].Text.Trim())
                            ]));
                        if (pagedResult.TotalRecords <= 0)
                        {
                            throw new Exception("部门不存在");
                        }

                        departmentId = pagedResult.Data.First().Id;
                    }

                    var teacherAddRequest = new TeacherAddRequest
                    {
                        TcrMajor = worksheet.Cells[row, 10].Text.Trim(),
                        TcrQual = worksheet.Cells[row, 11].Text.Trim(),
                        ColleId = collegeId,
                        TcrEduBg = string.IsNullOrEmpty(worksheet.Cells[row, 13].Text)
                            ? null
                            : worksheet.Cells[row, 13].Text,
                        TcrWorkExp = string.IsNullOrEmpty(worksheet.Cells[row, 14].Text)
                            ? null
                            : worksheet.Cells[row, 14].Text,
                        TcrWorkAddr = string.IsNullOrEmpty(worksheet.Cells[row, 15].Text)
                            ? null
                            : worksheet.Cells[row, 15].Text,
                        TcrEmpDate = DateTime.TryParse(worksheet.Cells[row, 16].Text, out var date1) ? date1 : null,
                        DepId = departmentId,
                        PsnId = personId,
                    };

                    var teacherId = await teacherRepository.InsertAsync(teacherAddRequest);
                    if (teacherId <= 0)
                    {
                        throw new Exception("插入教师信息失败");
                    }

                    var userAddRequest = new UserAddRequest
                    {
                        UserAccount = worksheet.Cells[row, 18].Text.Trim(),
                        UserPassword = worksheet.Cells[row, 19].Text.Trim(),
                        UserPhone = worksheet.Cells[row, 20].Text.Trim(),
                        UserRole = worksheet.Cells[row, 21].Text.Trim() == "教秘" ? "SECRET" : "TEACHER",
                        UserEmail = string.IsNullOrEmpty(worksheet.Cells[row, 22].Text)
                            ? null
                            : worksheet.Cells[row, 22].Text,
                        PsnId = personId
                    };

                    var userId = await userRepository.InsertAsync(userAddRequest);
                    if (userId <= 0)
                    {
                        throw new Exception("插入用户信息失败");
                    }
                }

                await cache.SetStringAsync("Task:" + taskRequest.TaskId, TaskStatus.Completed.ToString());
                await transactionManager.CommitTransactionAsync();
            }
            catch (Exception e)
            {
                await transactionManager.RollbackTransactionAsync();
                throw;
            }
        }
        catch (Exception e)
        {
            Console.WriteLine(e);
            await cache.SetStringAsync("Task:" + taskRequest.TaskId, TaskStatus.Failed.ToString());
        }
    }

    private static bool ValidateHeaderColumns(ExcelWorksheet worksheet,
        List<(string columnHeader, ExcelCellColor color)> templateHeaderColumns)
    {
        var worksheetHeaderColumns = new List<string>();
        var count = TeacherExcelCells.TipMessages.Count + 3;
        // 获取Excel工作表中实际的表头列名称
        for (var col = 1; col <= worksheet.Dimension.End.Column; col++)
        {
            if (!string.IsNullOrEmpty(worksheet.Cells[count, col].Text))
            {
                worksheetHeaderColumns.Add(worksheet.Cells[count, col].Text);
            }
        }

        if (worksheetHeaderColumns.Count != templateHeaderColumns.Count)
        {
            return false;
        }

        for (var i = 0; i < templateHeaderColumns.Count; i++)
        {
            if (worksheetHeaderColumns[i] != templateHeaderColumns[i].columnHeader)
            {
                return false;
            }
        }

        return true;
    }

    private static bool ValidateRequiredColumnsFilled(ExcelWorksheet worksheet,
        List<(string columnHeader, ExcelCellColor color)> templateHeaderColumns)
    {
        var requiredColumns = templateHeaderColumns.Where(c => c.color == ExcelCellColor.Black)
            .Select(c => c.columnHeader).ToList();
        var indexList = new List<int>();
        var count = TeacherExcelCells.TipMessages.Count + 3;
        for (var col = 1; col <= worksheet.Dimension.End.Column; col++)
        {
            if (!string.IsNullOrEmpty(worksheet.Cells[count, col].Text) &&
                requiredColumns.Contains(worksheet.Cells[count, col].Text))
            {
                indexList.Add(col);
            }
        }

        for (var row = count + 1; row <= worksheet.Dimension.End.Row; row++)
        {
            for (var col = 1; col <= worksheet.Dimension.End.Column; col++)
            {
                if (indexList.Contains(col) && string.IsNullOrEmpty(worksheet.Cells[row, col].Text))
                {
                    return false;
                }
            }
        }

        return true;
    }

    private static bool ValidateUniqueColumns(ExcelWorksheet worksheet,
        List<(string columnHeader, ExcelCellColor color)> templateHeaderColumns)
    {
        var uniqueColumns = templateHeaderColumns.Where(c => c.color == ExcelCellColor.Red).Select(c => c.columnHeader)
            .ToList();
        var indexList = new List<int>();
        var count = TeacherExcelCells.TipMessages.Count + 3;
        for (var col = 1; col <= worksheet.Dimension.End.Column; col++)
        {
            if (!string.IsNullOrEmpty(worksheet.Cells[count, col].Text) &&
                uniqueColumns.Contains(worksheet.Cells[count, col].Text))
            {
                indexList.Add(col);
            }
        }

        foreach (var i in indexList)
        {
            var dict = new HashSet<string>();
            for (var row = count + 1; row <= worksheet.Dimension.End.Row; row++)
            {
                if (string.IsNullOrWhiteSpace(worksheet.Cells[row, i].Text))
                {
                    continue;
                }

                var value = worksheet.Cells[row, i].Text;
                if (!dict.Add(value))
                {
                    return false;
                }
            }
        }

        return true;
    }
}